﻿/*----------------------------------------------------
 * Author : Tharinduni Udugamasuriya
 * FinCon dll for Handeling DB 
 * DatabaseAccess : Handeling DB Connection/Commands - outer
 ---------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DSPL.Contracts;
using System.Diagnostics;
using DSPL.Contracts.Common;
using Microsoft.Practices.Unity;
using DSPL.Contracts.Interfaces;

namespace DSPL.DataAccess
{
    public class DatabaseAccess : IDisposable
    {
        #region Construtor

        private static DatabaseAccess databaseAccess;

        public static  DatabaseAccess Instance
        {
            get
            {
                if (databaseAccess == null)
                {
                    databaseAccess = new DatabaseAccess();
                }

                return databaseAccess;
            }
        }

        private DatabaseAccess()
        {
            IUnityContainer container = GlobalVariables.GlobalUnityContainer;
            if (container != null)
            {
                messageService = container.Resolve<IMessageService>();
            }
            OpenConnection();
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            //Dispose(true);            
            CloseConnection();
            GC.SuppressFinalize(this);
        }

        //protected virtual void Dispose(bool disposing)
        //{
        //    if (disposed)
        //        return;

        //    if (disposing)
        //    {

        //    }

        //    disposed = true;
        //}

        ~DatabaseAccess()
        {
            //Dispose(false);
        }

        #endregion

        #region Decalare Private Variables

        IMessageService messageService;
        
        #endregion

        #region Decalare & Assign Public Variables

        //public static SqlConnection FinDbConn;

        private SqlConnection databaseConnection;

        #endregion

        #region Decalare & Assign Public Methods

        public void GetSQLConnection()
        {
            try
            {
                Connection con = new Connection();
                string conStr, Server, Db, UserName, Password;

                conStr = GlobalVariables.Connectionstring;
                Server = GlobalVariables.Server;
                Db = GlobalVariables.Database;
                UserName = GlobalVariables.UserName;
                Password = GlobalVariables.Password;

                if (conStr != "")
                {
                    databaseConnection = con.getSqlConnection(conStr);
                }
                else if (Server != "" && UserName != "" && Password != "" && Db != "")
                {
                    databaseConnection = con.getSqlConnection(Server, UserName, Password, true, Db);
                }
                else if (Server != "" && UserName != "" && Password != "")
                {
                    databaseConnection = con.getSqlConnection(Server, UserName, Password, false);
                }

                else
                {
                    messageService.ShowMessage(MessageText.InvalidDBSettings, MessageType.ErrOK);
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
        }

        public void OpenConnection()
        {
            try
            {
                if (databaseConnection == null)
                {
                    GetSQLConnection();
                    databaseConnection.Open();

                }
                else if (databaseConnection.State != ConnectionState.Open)
                {
                    databaseConnection.Open();
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

        }

        public void OpenConnection(ref SqlConnection DbCon)
        {
            try
            {
                if (DbCon == null)
                {
                    GetSQLConnection();
                    DbCon = databaseConnection;
                    DbCon.Open();

                }
                else if (DbCon.State != ConnectionState.Open)
                {
                    DbCon.Open();
                }
                else
                {
                    Exception e = new Exception("Invalid database settings found. Re-register database settings.");
                    throw e;
                }

            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
        }

        public bool CloseConnection()
        {
            try
            {
                if (databaseConnection.State != ConnectionState.Closed)
                {
                    databaseConnection.Close();
                    databaseConnection.Dispose();
                    SqlConnection.ClearAllPools();
                    return true;
                }
                else
                {
                    Exception e = new Exception("No open connection found.");
                    throw e;
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return false;
        }

        public bool CloseConnection(ref SqlConnection DbCon)
        {
            try
            {
                if (DbCon.State != ConnectionState.Closed)
                {
                    DbCon.Close();
                    DbCon.Dispose();
                    SqlConnection.ClearAllPools();
                    return true;
                }
                else
                {
                    Exception e = new Exception("No open connection found.");
                    throw e;
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return false;
        }

        public object ExcecuteScaler(SpCaller spCaller)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.ScalerDB(spCaller, ref databaseConnection);
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object ExcecuteScaler(Query query)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.ScalerDB(query, ref databaseConnection);
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object ExcecuteFunction(SpCaller spCaller)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.FunctionDB(spCaller, ref databaseConnection);
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public DataTable ExcecuteSelect(SpCaller spCaller)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.SelectDB(spCaller, ref databaseConnection);
                }
                else
                {

                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

            return null;

        }

        public DataTable ExcecuteSelect(Query query, bool isParameterized = false)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.SelectDB(query, ref databaseConnection, isParameterized);
                }

                else
                {

                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

            return null;

        }

        public DataTable ExcecuteSelect(SpCaller spCaller, bool timeOutOff)
        {
            try
            {
                if (!timeOutOff)
                {
                    return ExcecuteSelect(spCaller);
                }
                else
                {
                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }

                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Reader rd = new Reader();
                        return rd.SelectDB(spCaller, timeOutOff, ref databaseConnection);
                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

            return null;

        }

        public DataTable ExcecuteSelect(Query query, bool timeOutOff, bool isParameterized = false)
        {
            try
            {
                if (!timeOutOff)
                {
                    return ExcecuteSelect(query, isParameterized);
                }
                else
                {
                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }

                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Reader rd = new Reader();
                        return rd.SelectDB(query, timeOutOff, ref databaseConnection, isParameterized);
                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

            return null;
        }

        public DataTable ExcecuteSelect(SpCaller spCaller, ref SqlTransaction sqlTran)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = databaseConnection.BeginTransaction();
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.SelectDB(spCaller, ref sqlTran, ref databaseConnection);
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

            return null;
        }

        public DataTable ExcecuteSelect(Query query, ref SqlTransaction sqlTran, bool isParameterized = false)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = databaseConnection.BeginTransaction();
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Reader rd = new Reader();
                    return rd.SelectDB(query, ref sqlTran, ref databaseConnection, isParameterized);
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }

            return null;

        }

        public object Excecute(SpCaller spCaller)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }
                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Writer wt = new Writer();
                    return wt.Excecute(spCaller, ref databaseConnection);
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object Excecute(Query query, bool isParameterized = false)
        {
            try
            {
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }
                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Writer wt = new Writer();
                    return wt.Excecute(query, ref databaseConnection, isParameterized);
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object Excecute(SpCaller spCaller, string retPara)
        {
            try
            {
                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(spCaller);
                }
                else
                {
                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }

                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Writer wt = new Writer();
                        return wt.Excecute(spCaller, retPara, ref databaseConnection);
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }
                
        public object Excecute(SpCaller spCaller, string retPara, bool timeoutOff)
        {
            try
            {

                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(spCaller);
                }
                else if (!timeoutOff)
                {
                    return Excecute(spCaller, retPara);
                }
                else
                {
                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }
                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Writer wt = new Writer();
                        return wt.Excecute(spCaller, retPara, timeoutOff, ref databaseConnection);
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object Excecute(SpCaller spCaller, ref SqlTransaction sqlTran)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = databaseConnection.BeginTransaction();
                }
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Writer wt = new Writer();
                    return wt.Excecute(spCaller, ref sqlTran, ref databaseConnection);
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object Excecute(Query query, ref SqlTransaction sqlTran, bool isParameterized = false)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = databaseConnection.BeginTransaction();
                }
                if (databaseConnection.State != System.Data.ConnectionState.Open)
                {
                    OpenConnection(ref databaseConnection);
                }

                if (databaseConnection.State == System.Data.ConnectionState.Open)
                {
                    Writer wt = new Writer();
                    return wt.Excecute(query, ref sqlTran, ref databaseConnection, isParameterized);
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public object Excecute(SpCaller spCaller, string retPara, ref SqlTransaction sqlTran)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = databaseConnection.BeginTransaction();
                }


                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(spCaller, ref  sqlTran);
                }
                else
                {
                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }
                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Writer wt = new Writer();
                        return wt.Excecute(spCaller, retPara, ref sqlTran, ref databaseConnection);
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public Dictionary<string, object> ExcecuteMultipleOutputParameters(SpCaller spCaller)
        {
            try
            {
                if (spCaller != null)
                {
                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }

                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Writer wt = new Writer();
                        return wt.ExcecuteMultipleOutputParameters(spCaller, ref databaseConnection);
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public Dictionary<string, object> ExcecuteMultipleOutputParameters(SpCaller spCaller, bool timeoutOff)
        {
            try
            {
                if (!timeoutOff)
                    return ExcecuteMultipleOutputParameters(spCaller);
                else
                {
                    if (spCaller != null)
                    {
                        if (databaseConnection.State != System.Data.ConnectionState.Open)
                        {
                            OpenConnection(ref databaseConnection);
                        }

                        if (databaseConnection.State == System.Data.ConnectionState.Open)
                        {
                            Writer wt = new Writer();
                            return wt.ExcecuteMultipleOutputParameters(spCaller, timeoutOff, ref databaseConnection);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        public Dictionary<string, object> ExcecuteMultipleOutputParameters(SpCaller spCaller, ref SqlTransaction sqlTran)
        {
            try
            {
                if (spCaller != null)
                {
                    if (sqlTran == null)
                        sqlTran = databaseConnection.BeginTransaction();

                    if (databaseConnection.State != System.Data.ConnectionState.Open)
                    {
                        OpenConnection(ref databaseConnection);
                    }

                    if (databaseConnection.State == System.Data.ConnectionState.Open)
                    {
                        Writer wt = new Writer();
                        return wt.ExcecuteMultipleOutputParameters(spCaller, ref sqlTran, ref databaseConnection);
                    }
                }
            }
            catch (Exception ex)
            {
                messageService.ShowException(ex.Message.ToString(), new StackTrace().GetFrame(1).GetMethod().Name);
            }
            return null;
        }

        #endregion


    }
}
